
package cassandrawindow;

/**
 * @author ADMIN
 */
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.util.logging.Level;
import java.util.logging.Logger;

import java.util.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

import java.sql.Time;
import java.util.*;
import java.util.ArrayList;

public class MysqlDataManager5387 {

    private Connection conn = null;
    
    private String TABLE_BANNERS    = "ox_banners";
    private String TABLE_CAMPAIGN   = "ox_campaigns";
    
    private String V3_BAN_DATE      = "v3_ban_date";
    private String V3_BAN_HOURLY    = "v3_ban_hourly_vmask";

    private String V3_CAM_DATE      = "v3_cam_date";
    private String V3_CAM_HOURLY    = "v3_cam_hourly_vmask";
    
    private String V3_CAM_IP        = "v3_cam_ip";
    private String V3_DOMAIN_DATE   = "v3_domain_date";
    private String V3_ZONE_DATE     = "v3_zone_date";

    private String FRAUD_CLICK      = "fraud_click";
    private String BAN_PRO_TABLE    = "banlance_promotion";
    
    private String V3_BAN_HOURLY_TEST    = "v3_ban_hourly";
    private String V3_CAM_HOURLY_TEST    = "v3_cam_hourly";

    public MysqlDataManager5387()
    {
        /*try
        {
            Class.forName("com.mysql.jdbc.Driver").newInstance();            
            conn = DriverManager.getConnection(ACCOUNT.URL_MYSQL_5387 , ACCOUNT.username_mysql_5387, ACCOUNT.pass_mysql_main_5387);//abc!@#
            //Debug.println("Connected to MySQL Server database");
        }
        catch (Exception ex)
        {
            Debug.println("Couldn't connect to the MySQL database.  Make sure the MySQL connector jar is in the lib directory.  See the installation documentation for more information. ");
            Debug.println(ex.getMessage());
        }*/
        try {
            ConnectionPool connPool = new ConnectionPool(ACCOUNT.URL_MYSQL_5387 , ACCOUNT.username_mysql_5387, ACCOUNT.pass_mysql_main_5387);
            conn = connPool.getConnection();
            Debug.println("Connected to MySQL Server 53.87 database");

        } catch (SQLException ex) {
            ex.printStackTrace();
            Debug.println("Couldn't connect to the MySQL database 53.87.  Make sure the MySQL connector jar is in the lib directory.  See the installation documentation for more information. ");
            Debug.println(ex.getMessage());
            Debug.println(ex.getMessage());
        }
    }
    
    public void shutdownConnection()
    {
        try {
            conn.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    //for pega
    public void getDuplicateHotNews(String currentDate,Map hotNews)
    {
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT crawler_news_id, COUNT(*) c FROM new_cover_page WHERE DATE(created_date) = '"+ currentDate +"' GROUP BY crawler_news_id HAVING c >= 1");

            while (rs.next())
            {
                if(!checkVietNamNet(rs.getLong("crawler_news_id")))
                {
                    hotNews.put(rs.getLong("crawler_news_id"),rs.getLong("c"));
                } else{
                    Debug.println("Vietnamnet");
                }
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }
        
    }
    
    public Map getAllHotNewBySourceId(int id,String currentDate)
    {
        Map<Long, Long> allHotNewBySourceId = new HashMap<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT n.hot_news_id,n.crawler_news_id FROM new_cover_page n INNER JOIN crawler_hot_news c ON n.hot_news_id = c.id  WHERE c.status = 1 and DATE(c.created_date) = '"+ currentDate +"' and c.sourceid = "+id );
            //Debug.println("SELECT n.hot_news_id,n.crawler_news_id FROM new_cover_page n INNER JOIN crawler_hot_news c ON n.hot_news_id = c.id  WHERE c.status = 1 and DATE(c.created_date) = '"+ currentDate +"' and c.sourceid = "+id );
            while (rs.next())
            {
                allHotNewBySourceId.put(rs.getLong("n.hot_news_id"),rs.getLong("n.crawler_news_id"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }
        return allHotNewBySourceId;
    
    }
    
    public String getAllValueHotNewCoverPageByNewsId(long NewsId,int Rank,String currentDate)
    {
        String retVal = "";
        StringBuilder sb = new StringBuilder();
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select a.Source,b.Avatar,a.GeneralCatID,a.Type,a.CreatedDate,b.Id From NewsFeaturedRecomment a join ViewForAdmicro b on a.newsid=b.NewsId where DATE(a.CreatedDate) = '"+ currentDate +"' and b.NewsID = "+NewsId);
            while (rs.next())
            {
                sb.append("(").append(NewsId).append(",").
                append(rs.getInt("Source")).append(",").append(1).append(",").append("'").append(rs.getString("Avatar")).append("'").
                append(",").append(rs.getInt("Id")).append(",").append("'").append(rs.getString("CreatedDate")).append("'").append(",").append(-1).append(",").append(Rank).append(",").
                                                                                append(0).append(")");
            break;
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }

        return sb.toString();

    }

    public String getAllValueHotNewCoverPageByNewsIdV2(long NewsId,int Rank,String currentDate)
    {
        String retVal = "";
        StringBuilder sb = new StringBuilder();
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select a.Source,b.Avatar,a.GeneralCatID,a.CreatedDate,b.Id From NewsScoreRecomment a join ViewForAdmicro b on a.newsid=b.NewsId where DATE(a.CreatedDate) = '"+ currentDate +"' and b.NewsID = "+NewsId);
            while (rs.next())
            {
                sb.append("(").append(NewsId).append(",").
                append(rs.getInt("Source")).append(",").append(1).append(",").append("'").append(rs.getString("Avatar")).append("'").
                append(",").append(rs.getInt("Id")).append(",").append("'").append(rs.getString("CreatedDate")).append("'").append(",").append(-1).append(",").append(Rank).append(",").
                                                                                append(0).append(")");
            break;
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }

        return sb.toString();

    }

    public String getAllValueHotNewCoverPageByNewsIdV3(long NewsId,int Rank,String currentDate)
    {
        
        StringBuilder sb = new StringBuilder();
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select b.Source,b.Avatar,b.CreateDate,"
                    + "b.Id From ViewForAdmicro b "
                    + "where b.NewsID = "+NewsId);
            while (rs.next())
            {
                sb.append("(").append(NewsId).append(",").
                append(rs.getInt("Source")).append(",").append(1).append(",").append("'").append(rs.getString("Avatar")).append("'").
                append(",").append(rs.getInt("Id")).append(",").append("'").append(rs.getString("CreateDate")).append("'").append(",").append(-1).append(",").append(Rank).append(",").
                                                                                append(0).append(")");
            break;
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }

        return sb.toString();

    }


    public void getHotNewByCrawlerId(List infor,long id){
        //List infor = new ArrayList();       
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT hot_news_id FROM new_cover_page WHERE crawler_news_id = "+ id);
            while (rs.next())
            {
                infor.add(rs.getLong("hot_news_id"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }
        //return infor;
    }
    //end for pega
    public List getAllCampaignId()
    {
        List infor = new ArrayList();
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select campaignid from "+ TABLE_BANNERS);
            while (rs.next())
            {
                infor.add(rs.getString("campaignid"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }
        return infor;
    }

    public List getBannerIDByCampaignId(int campaignId)
    {
        List infor = new ArrayList();        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select bannerid from "+ TABLE_BANNERS +" where campaignid = "+campaignId+"");
            while (rs.next())
            {
                infor.add(rs.getString("bannerid"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }
        return infor;

    }

    public void insertDataBannerHour(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_BAN_HOURLY_TEST +"(bannerid,clickhour,viewhour,dt) values "+ value;
            //Debug.println(sql);
            s.execute(sql);            
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
        }
    }
    public void insertDataCampaignDay(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_CAM_DATE +"(campaignid,totalclick,totalview,realclick,clickcharge,viewcharge,uv,uc,dt,ctr,moneyc,moneyv) values "+ value;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
        }        
    }

    public void insertDataCampainHour(String value)
    {
         try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_CAM_HOURLY +"(campaignid,h,clicks,views,dt) values "+ value;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
        }        
    }

    public void insertDataCampaignAndBanner(String campaignid,String bannerid,String totalclick,String totalview, String dt)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_BAN_DATE +" values (" + Integer.valueOf(campaignid) + ", " + Integer.valueOf(bannerid) +"," + Integer.valueOf(totalclick) +",0,0," + Integer.valueOf(totalview) +",0,0,0,0,0,0,0,'" + dt + "',0)";
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
        }

    }

    public void insertSliceDataCampaignAndBanner(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_BAN_DATE +"(campaignid,bannerid,totalclick,realclick,clickcharge,totalview,viewcharge,uc,uv,MB,MT,MN,Other,dt,money) values " + value;
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
        }
    }

    public void insertDataCampaignIp(String value){
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_CAM_IP +"(campaignid, ip, dt) values "+ value ;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
        }
        
    }

    public void insertDataDomainData(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_DOMAIN_DATE +"(campaignid, bannerid, domain, totalclick, realclick, totalview, viewcharge, domaincrc, partnerid, istarget, domaingroup, domaingroupcrc, dt, preoder, money) values "+ value ;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
        }

    }

    public void insertDataZone(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_ZONE_DATE +"(campaignid, zoneid, bannerid, totalclick, realclick, clickcharge, totalview, viewcharge, dt, partnerid) values "+ value ;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
        }

    }

    public boolean checkExitsLineFraudClick(String line)
    {
        boolean retVal = false;
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select * from "+ FRAUD_CLICK +" where line = "+line+"");
            if (rs.next())
            {
                retVal = true;
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }
        return retVal;
    }

    public void insertFraudClick(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "insert ignore into "+ FRAUD_CLICK +"(line, guidid, zoneid, campaingnid, bannerid, domain, status, created_date) values "+ value;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
        }
    }

    public int getCountFraudClickByBannerIDAndDay(int bannerId, String Day)
    {
        int retValue = 0;
             
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select COUNT(*) from "+ FRAUD_CLICK +" where bannerid = " + bannerId + " AND created_date = '"+ Day +"' AND status = 0");            

            while (rs.next())
            {
                retValue = rs.getInt(1);                
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }
            
        return retValue;
    }

    public boolean updateFraudClick(int bannerId, String Day)
    {
        boolean check = false;
        int fraudClickCount = getCountFraudClickByBannerIDAndDay(bannerId, Day);
        Debug.printlnFaudClick("So fraukClick với bannerid "+ bannerId +" ngay " +Day +":"+fraudClickCount);
        //fraudClickCount = 100;
        try {
            Statement s = conn.createStatement();
            String str1 = "realclick = if((totalclick - "+ fraudClickCount+" ) > 0,totalclick - "+ fraudClickCount +" ,0)";
            String sql = "update  "+ V3_BAN_DATE +" set "+ str1 +" where bannerid = " + bannerId + " AND dt = '"+ Day +"'" ;
            check = s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
        }

        return check;
    }

    public List getValidBanner()
    {
        List addInfo = new ArrayList();
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select bannerid from "+ V3_BAN_DATE +" where totalclick  > 0");
            while (rs.next())
            {
                addInfo.add(rs.getString("bannerid"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }        
        return addInfo;
    }
    public Map getAllBannerHasDomain(String date)
    {
        Map<String,String> mp=new HashMap<String, String>();
        
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT bannerid,list_all_site FROM ox_banners WHERE DATE(updated) = "+ date +" ORDER BY bannerid DESC");
            while (rs.next())
            {
                mp.put(rs.getString("bannerid"), rs.getString("list_all_site"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }

        return mp;
    }

    public List getClickAndDomainByBannerid(String bannerid ,String date)
    {
        List CAD = new ArrayList<>();
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT domain,totalclick FROM "+V3_DOMAIN_DATE+" WHERE bannerid = "+ bannerid+" AND dt = '"+date+"'");            
            while (rs.next())
            {
                CAD.add(rs.getString("domain")+"_"+rs.getString("totalclick"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }

        return CAD;
    }

    public int getFraudClickByDomainAndBanner(String bannerid, String domain,String date)
    {
        int retValue = 0;
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("select COUNT(*) from "+ FRAUD_CLICK +" where bannerid = '"+bannerid+"' AND domain = '"+domain+"' AND created_date = '"+ date +"' AND status = 0");

            while (rs.next())
            {
                retValue = rs.getInt(1);
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }

        return retValue;
    }

    public Map getAllFraudClickByDay(String date)
    {
        Map<String, Integer> fraudclicks = new HashMap<>();
        
        int retValue = 0;
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT bannerid,domain,created_date, COUNT(*) FROM "+ FRAUD_CLICK +" WHERE created_date = '"+ date +"' GROUP BY bannerid,domain,created_date");

            while (rs.next())
            {
                String key = rs.getInt("bannerid")+"_"+rs.getString("domain")+"_"+date;
                fraudclicks.put(key,rs.getInt("COUNT(*)"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }

        return fraudclicks;
    }


    public Map getAllFraudClickByZone(String date)
    {
        Map<String, Integer> fraudclicks = new HashMap<>();

        int retValue = 0;
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT bannerid,zoneid,campaingnid,created_date, COUNT(*) FROM "+ FRAUD_CLICK +" WHERE created_date = '"+ date +"' GROUP BY bannerid,zoneid,campaingnid,created_date");

            while (rs.next())
            {
                String key = rs.getInt("bannerid")+"_"+rs.getString("campaingnid")+"_"+rs.getString("zoneid")+"_"+date;
                fraudclicks.put(key,rs.getInt("COUNT(*)"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }

        return fraudclicks;
    }

    public Map getAllFraudClickByBannerid(String date)
    {
        Map<String, Integer> fraudclicks = new HashMap<>();

        int retValue = 0;
        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT bannerid,created_date, COUNT(*) FROM "+ FRAUD_CLICK +" WHERE created_date = '"+ date +"' GROUP BY bannerid,created_date");

            while (rs.next())
            {
                String key = rs.getInt("bannerid")+"_"+date;
                fraudclicks.put(key,rs.getInt("COUNT(*)"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }

        return fraudclicks;
    }

    public Map getMoneyByBannerId(String date)
    {
        Map<String, Long> MoneyByBanner = new HashMap<>();

        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery(" SELECT bannerid,SUM(money) FROM v3_domain_date WHERE dt = '"+ date +"' GROUP BY bannerid,dt");

            while (rs.next())
            {
                MoneyByBanner.put(rs.getString("bannerid"), rs.getLong("SUM(money)"));
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }
        return MoneyByBanner;
    }
    public Map getMoneyByCampaignIdAndDomain(String date)
    {
            Map<String, String> MoneyByBanner = new HashMap<>();

            try
            {
                Statement st= (Statement)conn.createStatement();
                ResultSet rs = st.executeQuery("SELECT d.campaignid, d.domain, sum(realclick), sum(money), sum(totalview) FROM v3_domain_date d WHERE dt = '"+ date +"' GROUP BY d.campaignid ,d.domain");
                //Debug.println("SELECT d.campaignid, d.domain, sum(totalclick), sum(money) FROM v3_domain_date d WHERE dt = '"+ date +"' AND totalclick > 0 GROUP BY d.campaignid ,d.domain");
                while (rs.next())
                {
                    MoneyByBanner.put(rs.getString("d.campaignid")+"_"+rs.getString("d.domain"),rs.getString("sum(money)")+"_"+rs.getString("sum(realclick)")+"_"+rs.getString("sum(totalview)"));
                }
                rs.close();
                st.close();
            }
            catch (Exception ex)
            {
                Debug.println(ex.getMessage());
            }
            return MoneyByBanner;
    }

    public Map getClickByDomainDate(String date)
    {
         Map<String, Long> ClickByDomainDate = new HashMap<>();

        try
        {
            Statement st= (Statement)conn.createStatement();
            ResultSet rs = st.executeQuery(" SELECT bannerid,domain,totalclick FROM v3_domain_date WHERE dt = '"+ date +"' GROUP BY bannerid,dt");

            while (rs.next())
            {
                if(rs.getLong("totalclick") >0)
                {
                    ClickByDomainDate.put(rs.getString("bannerid")+"_"+rs.getString("domain")+"_"+date, rs.getLong("totalclick"));
                }
            }
            rs.close();
            st.close();
        }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }
        return ClickByDomainDate;
    }



    public void insertBalanceAndPromotionMoney(String value)
    {
        try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ BAN_PRO_TABLE +"(userid,campaignid,domain,totalclick,totalview,balance,promotions,groupid,dt) values " + value;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
        }
    }

    public Map getSimilityByID(long NewsID)
    {
        Map<Long, String> map = new HashMap<>();
        try
        {
                Statement s = conn.createStatement();
                String sql = "SELECT uid,GROUP_CONCAT(cmp) FROM ( SELECT DISTINCT C.campaignid AS cmp, A.uid AS uid Where    GROUP BY uid";
                ResultSet rs = s.executeQuery(sql);
                while (rs.next())
                {
                    map.put(rs.getLong("uid"),rs.getString("GROUP_CONCAT(cmp)"));
                }
                rs.close();
                s.close();
         }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }
        return map;
    }

    public void getNewsIDbyCat(int CatID,int limit,List newsList)
    {        
        try
        {
            Statement s = conn.createStatement();
            String sql = "SELECT NewsId FROM NewsFeaturedRecomment WHERE GeneralCatID = "+ CatID +" ORDER BY id desc limit "+limit;
            //Debug.println(sql);
            ResultSet rs = s.executeQuery(sql);
            while (rs.next())
            {
                newsList.add(rs.getLong("NewsId"));
            }
            rs.close();
            s.close();
         }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }        
    }

    public void getAllsimilityByNewsID(long NewsID,List infor)
    {
        //List infor = new ArrayList();
        try
        {
            Statement s = conn.createStatement();
            String sql = "SELECT second FROM bigdata_pair_simi where first = "+NewsID;
            ResultSet rs = s.executeQuery(sql);
            while (rs.next())
            {
                infor.add(rs.getLong("second"));
            }
            rs.close();
            s.close();
         }
        catch (Exception ex)
        {
            Debug.println(ex.getMessage());
        }        
    }
    

    public Map getRankChangeByTime(int cat,int limit)
    {
         Map<Long, Double> map = new HashMap<>();
            try
            {
                Statement s = conn.createStatement();
                String sql = "SELECT NewsId,Rank FROM NewsScoreRecomment WHERE DATE(CreatedDate) = CURDATE() and GeneralCatID = "+cat+" order by CreatedDate desc limit "+limit;
                Debug.println(sql);
                ResultSet rs = s.executeQuery(sql);
                while (rs.next())
                {
                    map.put(rs.getLong("NewsId"),rs.getDouble("Rank"));
                }
                rs.close();
                s.close();
             }
            catch (Exception ex)
            {
                Debug.println(ex.getMessage());
            }
         return map;
    }

    public boolean checkVietNamNet(long NewsId)
    {
            boolean retVal = false;
            try
            {
                Statement s = conn.createStatement();
                String sql = "SELECT * FROM ViewForAdmicro WHERE NewsUrl LIKE '%vietnamnet%' AND NewsID = "+NewsId;
                ResultSet rs = s.executeQuery(sql);
                while (rs.next())
                {
                    retVal = true;
                }
                rs.close();
                s.close();
             }
            catch (Exception ex)
            {
                Debug.println(ex.getMessage());
            }

            return retVal;
    }

    public void insertDataCampainHourForCPC(String value)
    {
         try {
            Statement s = conn.createStatement();
            String sql = "replace into "+ V3_CAM_HOURLY_TEST +"(campaignid,h,clicks,views,dt) values "+ value;
            //Debug.println(sql);
            s.execute(sql);
            s.close();
        } catch (Exception ex)
        {
          Debug.println(ex.getMessage());
        }
    }
}
